﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;

namespace outREACH.App_Code
{
    public class EduAnnouncement
    {
        public string title { get; set; }
        public string text { get; set; }
        public string userName { get; set; }
        public DateTime dateTime { get; set; }
        public string replyUser { get; set; }
        public string reply { get; set; }
        public string replyTitle { get; set; }

        public static List<EduAnnouncement> getEduAnnouncement()
        {
            SqlConnection conn = null;
            List<EduAnnouncement> result = new List<EduAnnouncement>();
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from EducationAnnoucement order by dateTime desc";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    EduAnnouncement ea = new EduAnnouncement();
                    if ((string)dr["title"] == null)
                    {
                        ea.title = "";
                    }
                    else
                    {
                        ea.title = (string)dr["title"];
                    }

                    if ((string)dr["text"] == null)
                    {
                        ea.text = "";
                    }
                    else
                    {
                        ea.text = (string)dr["text"];
                    }

                    if ((string)dr["userName"] == null)
                    {
                        ea.userName = "";
                    }
                    else
                    {
                        ea.userName = (string)dr["userName"];
                    }

                    ea.dateTime = (DateTime)dr["dateTime"];

                    if ((string)dr["replyUser"] == null)
                    {
                        ea.replyUser = "";
                    }
                    else
                    {
                        ea.replyUser = (string)dr["replyUser"];
                    }

                    if ((string)dr["reply"] == null)
                    {
                        ea.reply = "";
                    }
                    else
                    {
                        ea.reply = (string)dr["reply"];
                    }

                    if ((string)dr["replyTitle"] == null)
                    {
                        ea.replyTitle = "";
                    }
                    else
                    {
                        ea.replyTitle = (string)dr["replyTitle"];
                    }
                    result.Add(ea);
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public static void InsertEduAnnoucement(string title, string text, DateTime dateTime, string userName, string replyUser, string reply, string replyTitle)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "insert into EducationAnnoucement(title, text, dateTime, userName, replyUser, reply, replyTitle) VALUES (@title, @text, @dateTime, @userName, @replyUser, @reply, @replyTitle)";
                comm.Parameters.AddWithValue("@userName", userName);
                comm.Parameters.AddWithValue("@title", title);
                comm.Parameters.AddWithValue("@text", text);
                comm.Parameters.AddWithValue("@dateTime", dateTime);
                comm.Parameters.AddWithValue("@replyUser", replyUser);
                comm.Parameters.AddWithValue("@reply", reply);
                comm.Parameters.AddWithValue("@replyTitle", replyTitle);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
    }
}